﻿using Model;
using Model.CustomDataType;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class PatientDA : DBConnection
    {
        
        public PatientDA()
        {
            sqlCommand.Connection = sqlConnection;
        }
        
        /// <summary>
        /// Add a new patient to database
        /// </summary>
        /// <param name="patient">The new patient to add </param>
        /// <returns>true if adding successfully, false otherwise</returns>
        public int addPatient(Patient patient)
        {
            string query = "insert into " + Common.BENH_NHAN +
                " values(@fullname,@idcard,@sex,@dateofbirth,@address, @phonenumber, @relative); select SCOPE_IDENTITY();";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@fullname", patient.getFullName());
            sqlCommand.Parameters.AddWithValue("@idcard", patient.getIdCard());
            sqlCommand.Parameters.AddWithValue("@sex", Utils.convertSexEnumToInt(patient.getSex()));
            CMDateTime myDob = patient.getDateOfBirth();
            DateTime dob = Utils.convertCMDateTimeToDateTime(myDob);
            sqlCommand.Parameters.AddWithValue("@dateofbirth", dob);
            string address = patient.getAddress();
            if (address == null || address == "")
                address = "Chưa cập nhật";
            sqlCommand.Parameters.AddWithValue("@address", address);
            string phone = patient.getPhoneNumber();
            if (phone == null || phone == "")
                phone = "0";
            sqlCommand.Parameters.AddWithValue("@phonenumber", phone);
            string rel = patient.getRelative();
            if (rel == null || rel == "")
                rel = "Chưa cập nhật";
            sqlCommand.Parameters.AddWithValue("@relative", rel);
            try
            {
                open();
                int idRe = -1; 
                sqlCommand.CommandText = query;
                string re = sqlCommand.ExecuteScalar().ToString();
                idRe = Int32.Parse(re);
                close();
                return idRe;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return -1;
            }
        }

        public bool updatePatient(Patient patient)
        {
            //log here
            int patientId = patient.getPatientId();
            string query = "update " + Common.BENH_NHAN +
                " set fullname = @fullname, " +
                "idcard = @idcard,sex = @sex,dateofbirth = @dateofbirth,address = @address," +
                "phonenumber = @phonenumber, relative = @relative " +
                "where patientid = " + patientId;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@fullname", patient.getFullName());
            sqlCommand.Parameters.AddWithValue("@idcard", patient.getIdCard());
            sqlCommand.Parameters.AddWithValue("@sex", Utils.convertSexEnumToInt(patient.getSex()));
            CMDateTime myDob = patient.getDateOfBirth();
            DateTime dob = Utils.convertCMDateTimeToDateTime(myDob);
            sqlCommand.Parameters.AddWithValue("@dateofbirth", dob);
            string address = patient.getAddress();
            if (address == null || address == "")
                address = "Chưa cập nhật";
            sqlCommand.Parameters.AddWithValue("@address", address);
            string phone = patient.getPhoneNumber();
            if (phone == null || phone == "")
                phone = "0";
            sqlCommand.Parameters.AddWithValue("@phonenumber", phone);
            string rel = patient.getRelative();
            if (rel == null || rel == "")
                rel = "Chưa cập nhật";
            sqlCommand.Parameters.AddWithValue("@relative", rel);

            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                Console.WriteLine(s.Message);
                return false;
            }
        }

        public Patient getPatientById(int patientId)
        {
            string query = "select * from " + Common.BENH_NHAN + " where patientid=" + patientId;
            sqlCommand.CommandText = query;
            try
            {

                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                Patient patient = new Patient();
                while (sqlDataReader.Read())
                {
                    patient = createPatientFromReader(sqlDataReader);

                }
                close();
                return patient;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }


        }

        public Patient[] getAllPatient()
        {
            string query = "select * from " + Common.BENH_NHAN;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Patient> liPatient = new List<Patient>();
                Patient patient  = new Patient();
                while (sqlDataReader.Read())
                {
                    patient = createPatientFromReader(sqlDataReader);
                    liPatient.Add(patient);
                }
                close();
                return liPatient.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }


        }

        public Patient[] getPatientsByDate(CMDateTime date)
        {
            string query = "select * from " + Common.BENH_NHAN +
                " where dateofbirth = '" + Utils.convertCMDateTimeToDateTime(date) + "'";
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Patient> liPatient = new List<Patient>();
                Patient patient = new Patient();
                while (sqlDataReader.Read())
                {
                    patient = createPatientFromReader(sqlDataReader);
                    liPatient.Add(patient);
                }
                close();
                return liPatient.ToArray();
            }
            catch
            {
                //log here
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
            
        }

        /// <summary>
        /// Get an array of patients from a custom query
        /// </summary>
        /// <param name="query">The custom query</param>
        /// <returns>An array of patients</returns>
        public Patient[] getPatientsByRawQuery(string query)
        {
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Patient> liPatient = new List<Patient>();
                Patient patient = null;
                while (sqlDataReader.Read())
                {
                    string str = "";
                    patient = new Patient();
                    for (int i = 0; i < sqlDataReader.FieldCount; i++)
                    {
                        str = sqlDataReader.GetName(i);//Get name of column;

                        str.ToLower();
                        switch (str)
                        {
                            case "patientid":
                                patient.setPatientId(Int32.Parse(sqlDataReader.GetValue(i).ToString()));
                                break;
                            case "fullname":
                                patient.setFullName(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "idcard":
                                patient.setIdCard(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "sex":
                                patient.setSex(Utils.convertIntToSexEnum(
                                    Int16.Parse(sqlDataReader.GetValue(i).ToString())));
                                break;
                            case "dateofbirth":
                                patient.setDateOfBirth(Utils.convertDateTimeToCMDateTime((DateTime)sqlDataReader.GetValue(i)));
                                break;
                            case "address":
                                patient.setAddress(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "phonenumber":
                                patient.setPhoneNumber(sqlDataReader.GetValue(i).ToString());
                                break;
                            case "relative":
                                patient.setRelative(sqlDataReader.GetValue(i).ToString());
                                break;
                        }
                    }
                    liPatient.Add(patient);
                }
                close();
                return liPatient.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }

        private Patient createPatientFromReader(SqlDataReader rd)
        {

            if (rd != null && !rd.IsClosed)
            {
                Patient patient = new Patient();
                patient.setPatientId(rd.GetInt32(0));
                patient.setFullName(rd.GetString(1));
                patient.setIdCard(rd.GetString(2));
                patient.setSex(Utils.convertIntToSexEnum(rd.GetInt32(3)));
                DateTime dt = rd.GetDateTime(4);
                patient.setDateOfBirth(Utils.convertDateTimeToCMDateTime(dt));
                patient.setAddress(rd.GetString(5));
                patient.setPhoneNumber(rd.GetString(6));
                patient.setRelative(rd.GetString(7));
                return patient;
            }
            else
            {
                return null;
            }

        }
    }
}
